/*
 * This File is part of Innovation Foundry's "QualityHelper Project"
 *
 * Copyright  2008 Innovation Foundry Pty Ltd
 * All rights reserved

 * This work is free software; you can redistribute it and/or modify 
 * it under the terms of the GNU General Public License as published 
 * by the Free Software Foundation; either version 3 of the License, 
 * or any later version. This work is distributed in the hope that 
 * it will be useful, but WITHOUT ANY WARRANTY; without even the 
 * implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR 
 * PURPOSE. 
 * See version 3 of the GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this work; if not, write to the
 * Free Software Foundation, Inc., 51 Franklin Street, Fifth Floor Boston, MA  02110-1301 USA
 * or alternatively it may be downloaded from <http://www.gnu.org/licenses/>.
 *
 * Notes:
 *
 * Examples of fts2 query syntax:
 *	cheese tomatoes (Find rows containing 'cheese' and 'tomatoes' in any field.)
 *	dish:stew tomatoes (Find rows with 'stew' in the dish field, and 'tomatoes' in any field.)
 *	cheese OR tomatoes (Find rows containing either 'cheese' or 'tomatoes' in any field. Note that OR operator must be capitalized.)
 *	"green tomatoes" (Find rows where 'green' is immediately followed by 'tomatoes', in any single field.)
 *	cheese -tomatoes (Find rows containing 'cheese' in any field, and not containing 'tomatoes' in any field.)
 *	ch* (Find rows containing words which start with 'ch', including rows containing 'cheese' or 'chowder'. The '*' must come at the end of the word. 
 *
 *
 */
(function() {
    var applicationBackground = 'ifbg.jpg'; //use background.jpg to use internal silverlight bg //null will make it black
    var applicationAlias = 'QualityHelper'; //Only letters allowed
    var applicationIcon = 'qh64.png';
    var helpDoc = './QualityHelper.pdf'; //If its a number will cache from docs (otherwise will use url)
    var inheritsOrphanReference = true;
    var debuggingOn = false;
    var PREFIX = '*++';
    var SUFFIX = '++*';
    var RootProcedureID = 'Innovation Foundry';
    var CurrentSchemaVersion = 1;
    var DBSchemaName = 'qualityhelper_dbschema_version';
    var StoreName = 'qualityhelper-store';
    var DBName = 'qualityhelper-db';
    var ttLastProcedureClicked = 'LastProcedureClicked';
    var ttLatestDiagrams = 'LatestDiagramDownload';
    var ttLastFullDocs = 'LastFullDocumentDownload';
    var FilesToCapture = [
        'Silverlight.js',
        'google_tools.js',
        'qualityhelper_classes.js',
        'gears_init.js',
        'data_manager.js',
        'prototype/prototype.js',
        'if_tools.js',
        'ClientBin/QualityHelperBaseSilverlight.xap'
        ];
    try {
        FilesToCapture[FilesToCapture.length] = location.pathname;
    }
    catch (e) { }


    function IFDM(isWorker) {
        this.HelpDoc = helpDoc;
        this.DebuggingOn = debuggingOn;
        this.ApplicationAlias = applicationAlias;
        this.ApplicationIcon = applicationIcon;
        this.ApplicationBackground = applicationBackground;
        this.InheritsOrphanReference = inheritsOrphanReference;
        this.db = null;
        this.dbSchemaVersion = 0;
        this.dbCached = false;
        this.hasDB = false;
        this.hasValidDB = false;
        this.canGoLocal = false;
        this.localServer = null;
        this.localStore = null;
        this.isWorker = isWorker;
        if (isWorker) {
            this.db_check();
            this.store_init();
        }
        else {
            this.db_init();
            this.db_upgrade();
            this.store_init();
        }
        this.all_purposes = this.db_selectAllTo_purposes();
        this.all_groups = this.db_selectAllTo_groups();
        this.all_connectionTypes = this.db_selectAllTo_connectionTypes();

    }

    IFDM.prototype.store_init = function() {
        this.localServer = google.gears.factory.create('beta.localserver');
        this.localStore = this.localServer.createStore(StoreName);
    }

    IFDM.prototype.store_clear = function() {
        this.localServer.removeStore(StoreName);
        this.localStore = this.localServer.createStore(StoreName);
    }

    IFDM.prototype.db_init = function() {
        if (window.google && google.gears) {
            if (this.db_check()) {
                this.db_check_stats();
            }

            if (this.dbSchemaVersion == 0 || !this.hasDB) {
                this.db_reset();
            }

        }
    };

    IFDM.prototype.db_check = function() {
        try {
            this.db = google.gears.factory.create('beta.database');
            if (this.db) {
                this.db.open(DBName);
                this.hasDB = true;
                return true;
            }
        }
        catch (ex) {
            if (this.DebuggingOn)
                setError('Could not create database: ' + ex.message);
        }
        return false;
    };

    IFDM.prototype.db_check_stats = function() {
        try {
            var rslt = this.executeToObjects('select * from if_translateTable where TTKey=?', [DBSchemaName]);
            if (rslt) {
                for (var i = 0; i < rslt.length; i++) {
                    if (rslt[i].TTKey == DBSchemaName) {
                        this.dbSchemaVersion = Number(rslt[i].TTValue);
                        trace('schema version:' + this.dbSchemaVersion);
                        if (this.dbSchemaVersion == CurrentSchemaVersion) {
                            this.hasValidDB = true;
                        }
                        //this.dbVersion = Number(rslt[i].name.match(/version_(\d+)/)[1]);
                    }
                }
            }

        }
        catch (e) {
            this.hasValidDB = false;
        }
    };


    // Enable or disable UI elements
    IFDM.prototype.gui_init = function() {
        var inputs = document.forms[0].elements;
        for (var i = 0, el; el = inputs[i]; i++) {
            el.disabled = !success;
        }
    }

    //Get the default root procedure
    IFDM.prototype.get_rootprocedureid = function() {
        return RootProcedureID;
    }

    //Transaction Start
    IFDM.prototype.db_transaction_start = function() {
        this.execute('BEGIN');
    }

    //Transaction Commit
    IFDM.prototype.db_transaction_commit = function() {
        this.execute('COMMIT');
    }

    //Transaction Rollback
    IFDM.prototype.db_transaction_rollback = function() {
        this.execute('ROLLBACK');
    }

    IFDM.prototype.db_close = function() {
        this.db.close();
    }

    IFDM.prototype.execute = function(sql, args) {
        if (this.hasDB) {
            trace('sql: %s, args: %s', sql, args);
            return this.db.execute(sql, args);
        }
    };

    //Returns boolean
    IFDM.prototype.executeWithTry = function(sql, args) {
        try {
            trace('sql: %s, args: %s', sql, args);
            this.db.execute(sql, args);
            return true;
        }
        catch (ex) {
            if (this.DebuggingOn)
                setError('Could not execute sql: ' + ex.message);
            return false;
        }
    }


    IFDM.prototype.executeToObjects = function(sql, args) {
        var rs = this.execute(sql, args);
        try {
            var rv = [];
            if (rs && rs.isValidRow()) {
                var cols = rs.fieldCount();
                var colNames = [];
                for (var i = 0; i < cols; i++) {
                    colNames.push(rs.fieldName(i));
                }

                while (rs.isValidRow()) {
                    var h = {};
                    for (i = 0; i < cols; i++) {
                        h[colNames[i]] = rs.field(i);
                    }
                    rv.push(h);
                    rs.next();
                }
            }
        } catch (e) {
            throw e;
        } finally {
            rs.close();
            return rv;
        }
    }

    IFDM.prototype.db_reset = function() {
        this.dbSchemaVersion = 0;
        this.db_tables_drop();
        this.db_tables_init();
        this.db_data_init();
        this.db_check_stats();
        if (this.dbSchemaVersion == 0) {
            trace('Could not initialize database.');
        }
    }


    /*
    * PROJECT SPECIFIC CODE STARTS HERE
    *
    *
    *
    *
    */

    IFDM.prototype.db_upgrade = function() {
        if (!this.hasDB || this.dbSchemaVersion == 0) {
            trace('Can\'t upgrade, the database is not initialized');
            return;
        }

        if (this.dbSchemaVersion < CurrentSchemaVersion) {
            // Upgrade users from version 1 to 2
            if (this.dbSchemaVersion == 1) {
                this.db_transaction_start();
                //Do something here when we upgrade Example...
                this.execute('create table testTableVersion2 (testcol1 text)');
                //this.execute("UPDATE if_translateTable SET TTValue=? WHERE TTKey=?", [CurrentSchemaVersion, DBSchemaName]);
                this.db_insert_translateTable(DBSchemaName, null, null, CurrentSchemaVersion, true, false);
                this.db_transaction_commit();
            }

        }

    }

    //Drop all tables
    IFDM.prototype.db_tables_drop = function() {
        this.executeWithTry("DROP TABLE if_procedureAliases_aux");
        this.executeWithTry("DROP TABLE if_document_aux");
        this.executeWithTry("DROP TABLE if_procedure_aux_id");
        this.executeWithTry("DROP TABLE if_procedure_aux_name");
        this.executeWithTry("DROP TABLE if_procedure_aux_description");
        this.executeWithTry("DROP TABLE IF_Connects");
        this.executeWithTry("DROP TABLE IF_References");
        this.executeWithTry("DROP TABLE IF_ProcedureAliases");
        this.executeWithTry("DROP TABLE IF_SystemPolicy");
        this.executeWithTry("DROP TABLE IF_PolicyHierarchy");
        this.executeWithTry("DROP TABLE IF_ProcedureCompliance");
        this.executeWithTry("DROP TABLE IF_Participants");
        this.executeWithTry("DROP TABLE IF_GroupHierarchy");
        this.executeWithTry("DROP TABLE IF_ProcedureGroup");
        this.executeWithTry("DROP TABLE IF_TranslateTable");
        this.executeWithTry("DROP TABLE IF_ProcedureDocuments");
        this.executeWithTry("DROP TABLE IF_ConnectionType");
        this.executeWithTry("DROP TABLE IF_Procedure");
        this.executeWithTry("DROP TABLE IF_System");
        this.executeWithTry("DROP TABLE IF_Policy");
        this.executeWithTry("DROP TABLE IF_Group");
        this.executeWithTry("DROP TABLE IF_User");
        this.executeWithTry("DROP TABLE IF_Purpose");
        this.executeWithTry("DROP TABLE IF_MimeExtensions");
        this.executeWithTry("DROP TABLE IF_DocumentType");
        this.executeWithTry("DROP TABLE IF_MimeType");
        this.executeWithTry("DROP TABLE IF_Document");
    }


    IFDM.prototype.db_data_init = function() {
        this.executeWithTry("INSERT INTO if_translateTable (TTKey,TTObjectType,TTValue) values (?,?,?)", [DBSchemaName, 'System.Int32', CurrentSchemaVersion]);
    }

    //create table schema
    IFDM.prototype.db_tables_init = function() {
        //Base Entitities
        this.executeWithTry("create table if not exists if_connectionType (ConnectionType nvarchar(20) NOT NULL PRIMARY KEY ASC, ColorRGBHex nchar(6) DEFAULT '000000' NOT NULL)");
        this.executeWithTry("create table if not exists if_group (GroupName nvarchar(20) NOT NULL PRIMARY KEY ASC)");
        this.executeWithTry("create table if not exists if_user (Username nvarchar(30) NOT NULL PRIMARY KEY ASC)");
        this.executeWithTry("create table if not exists if_translateTable (TTKey nvarchar(50) NOT NULL PRIMARY KEY ASC, TTSupplement nvarchar(50), TTObjectType nvarchar(250), TTValue ntext, IsPrivate boolean not null default 0, IsReadOnly boolean not null default 1)");
        this.executeWithTry("create table if not exists if_system(SystemName nvarchar(50) NOT NULL PRIMARY KEY ASC, SystemDescription nvarchar(250))");
        this.executeWithTry("create table if not exists if_policy(PolicyName nvarchar(30) NOT NULL PRIMARY KEY ASC, PolicyDescription nvarchar(250))");
        this.executeWithTry("create table if not exists if_documentType(DocumentType nvarchar(20) NOT NULL PRIMARY KEY ASC, ImageURL varchar(500))");
        this.executeWithTry("create table if not exists if_purpose(PurposeName nvarchar(20) NOT NULL PRIMARY KEY ASC, FlowchartElement nvarchar(20))");
        //Aggregate Entities
        this.executeWithTry("create table if not exists if_connects(ProcedureOrigin nvarchar(20) NOT NULL, ProcedureDestination nvarchar(20) NOT NULL, ConnectionType nvarchar(20), PRIMARY KEY (ProcedureOrigin ASC, ProcedureDestination ASC))");
        this.executeWithTry("create table if not exists if_references(ProcedureOrigin nvarchar(20) NOT NULL, ProcedureDestination nvarchar(20) NOT NULL, PRIMARY KEY (ProcedureOrigin ASC, ProcedureDestination ASC))");
        this.executeWithTry("create table if not exists if_participants(Username nvarchar(30) NOT NULL, GroupName nvarchar(20) NOT NULL, PRIMARY KEY (Username ASC, GroupName ASC))");
        this.executeWithTry("create table if not exists if_groupHierarchy(ParentGroupName nvarchar(20) NOT NULL, ChildGroupName nvarchar(20) NOT NULL, PRIMARY KEY (ParentGroupName ASC, ChildGroupName ASC))");
        this.executeWithTry("create table if not exists if_procedureGroup(ProcedureID nvarchar(20) NOT NULL, GroupName nvarchar(20) NOT NULL, PRIMARY KEY (ProcedureID ASC, GroupName ASC))");
        this.executeWithTry("create table if not exists if_procedureAliases(ProcedureID nvarchar(20) NOT NULL, Alias nvarchar(30) NOT NULL, PRIMARY KEY (ProcedureID ASC, Alias ASC))")
        this.executeWithTry("create virtual table if_procedureAliases_aux USING fts2(ProcedureID nvarchar(20) NOT NULL, Alias nvarchar(30) NOT NULL)");
        this.executeWithTry("create table if not exists if_procedureDocuments(ProcedureID nvarchar(20) NOT NULL, DocumentID INTEGER NOT NULL, PRIMARY KEY (ProcedureID ASC, DocumentID ASC))");
        this.executeWithTry("create table if not exists if_procedureCompliance(ProcedureID nvarchar(20) NOT NULL, PolicyName nvarchar(30) NOT NULL, PRIMARY KEY (ProcedureID ASC, PolicyName ASC))");
        this.executeWithTry("create table if not exists if_systemPolicy(SystemName nvarchar(50) NOT NULL, PolicyName nvarchar(30) NOT NULL, PRIMARY KEY (SystemName ASC, PolicyName ASC))");
        this.executeWithTry("create table if not exists if_policyHierarchy(ParentPolicyName nvarchar(30) NOT NULL, ChildPolicyName nvarchar(30) NOT NULL, PRIMARY KEY (ParentPolicyName,ChildPolicyName))");
        this.executeWithTry("create table if not exists if_mimeType(MimeType nvarchar(30) not null PRIMARY KEY ASC, DocumentType nvarchar(20) not null)");
        this.executeWithTry("create table if not exists if_mimeExtensions(DocumentExtension varchar(12) NOT NULL PRIMARY KEY ASC, MimeType nvarchar(30) NOT NULL)");
        this.executeWithTry("create table if not exists if_document(DocumentID INTEGER NOT NULL PRIMARY KEY ASC, DocumentVersion INTEGER DEFAULT -1, DocumentFileID varchar(64), MD5Checksum varchar(32), DateUpdated INTEGER DEFAULT 20001231235959,  DocumentExtension varchar(12))"); // GETS ID FROM SERVER
        this.executeWithTry("create virtual table if_document_aux USING fts2(URL varchar(500) NOT NULL PRIMARY KEY ASC, DocumentName nvarchar(50) NOT NULL, DocumentDescription nvarchar(250), ImageURL varchar(500), Cache TEXT)"); //REQUIRES DOCID INTO ROWID
        //this.executeWithTry("create index if not exists if_ix_documentName on if_document(DocumentName)");
        this.executeWithTry("create table if not exists if_procedure(ProcedureID nvarchar(20) NOT NULL PRIMARY KEY ASC, ProcedureName nvarchar(30), ProcedureDescription nvarchar(250), PurposeName nvarchar(20))");
        this.executeWithTry("create virtual table if_procedure_aux_id USING fts2(ProcedureID)");
        this.executeWithTry("create virtual table if_procedure_aux_name USING fts2(ProcedureName)");
        this.executeWithTry("create virtual table if_procedure_aux_description USING fts2(ProcedureDescription)");
    }

    //Inserts for all tables
    IFDM.prototype.db_insert_connectionType = function(connectiontype, colorrgbhex) {
        this.executeWithTry("INSERT INTO if_connectionType (connectionType, colorRGBHex) values (?,?)", [connectiontype, colorrgbhex]);
    }

    IFDM.prototype.db_insert_group = function(groupname) {
        this.executeWithTry("INSERT INTO if_group (groupName) values (?)", [groupname]);
    }

    IFDM.prototype.db_insert_user = function(user) {
        this.executeWithTry("INSERT INTO if_user (Username) values (?)", [user]);
    }

    IFDM.prototype.db_insert_translateTable = function(ttkey, ttsupplement, ttobject, ttvalue, isprivate, isreadonly) {
        var inserted = this.executeWithTry("INSERT INTO if_translateTable (ttkey,ttsupplement,ttobjecttype,ttvalue,isprivate,isreadonly) values (?,?,?,?,?,?)", [ttkey, ttsupplement, ttobject, ttvalue, isprivate, isreadonly]);
        if (!inserted) {
            //update instead
            this.executeWithTry("UPDATE if_translateTable SET ttsupplement=?,ttobjecttype=?,ttvalue=?,isprivate=?,isreadonly=? where ttkey=?", [ttsupplement, ttobject, ttvalue, isprivate, isreadonly, ttkey]);
        }
    }

    IFDM.prototype.db_update_lastProcedureClicked = function(lastProcedureClicked) {
        this.db_insert_translateTable(ttLastProcedureClicked, null, null, lastProcedureClicked, true, false);
    }

    IFDM.prototype.db_update_latestDiagrams = function(longDateTime) {
        this.db_insert_translateTable(ttLatestDiagrams, null, null, longDateTime, true, false);
    }

    IFDM.prototype.db_update_lastFullDocuments = function(longDateTime) {
        this.db_insert_translateTable(ttLastFullDocs, null, null, longDateTime, true, false);
    }

    IFDM.prototype.db_insert_system = function(systemname, systemdescription) {
        this.executeWithTry("INSERT INTO if_system (systemName, systemDescription) values (?,?)", [systemname, systemdescription]);
    }

    IFDM.prototype.db_insert_policy = function(policyname, policydescription) {
        this.executeWithTry("INSERT INTO if_policy (policyName, policyDescription) values (?,?)", [policyname, policydescription]);
    }

    IFDM.prototype.db_insert_documentType = function(documenttype, imageurl) {
        this.executeWithTry("INSERT INTO if_documentType (documentType, imageURL) values (?,?)", [documenttype, imageurl]);
    }

    IFDM.prototype.db_insert_purpose = function(purposename, flowchartelement) {
        this.executeWithTry("INSERT INTO if_purpose (purposeName, FlowchartElement) values (?,?)", [purposename, flowchartelement]);
    }

    IFDM.prototype.db_insert_connects = function(procedureorigin, proceduredestination, connectiontype) {
        this.executeWithTry("INSERT INTO if_connects (procedureOrigin, procedureDestination, connectionType) values (?,?,?)", [procedureorigin, proceduredestination, connectiontype]);
    }

    IFDM.prototype.db_insert_references = function(procedureorigin, proceduredestination) {
        this.executeWithTry("INSERT INTO if_references (procedureOrigin, procedureDestination) values (?,?)", [procedureorigin, proceduredestination]);
    }

    IFDM.prototype.db_insert_participants = function(username, groupname) {
        this.executeWithTry("INSERT INTO if_participants (username,groupname) values (?,?)", [username, groupname]);
    }

    IFDM.prototype.db_insert_groupHierarchy = function(parentgroupname, childgroupname) {
        this.executeWithTry("INSERT INTO if_groupHierarchy (parentGroupName,childGroupName) values (?,?)", [parentgroupname, childgroupname]);
    }

    IFDM.prototype.db_insert_procedureGroup = function(procedureid, groupname) {
        this.executeWithTry("INSERT INTO if_procedureGroup (procedureID,groupName) values (?,?)", [procedureid, groupname]);
    }

    IFDM.prototype.db_insert_procedureAliases = function(procedureid, alias) {
        this.execute("INSERT INTO if_procedureAliases (procedureID,alias) values (?,?)", [procedureid, alias]);
        this.execute('INSERT INTO if_procedureAliases_aux (rowid, procedureID, alias) VALUES (last_insert_rowid(), ?, ?)', [procedureid, alias]);
    }

    IFDM.prototype.db_insert_procedureDocuments = function(procedureid, documentid) {
        this.executeWithTry("INSERT INTO if_procedureDocuments (procedureID,documentID) values (?,?)", [procedureid, documentid]);
    }

    IFDM.prototype.db_insert_procedureCompliance = function(procedureid, policyname) {
        this.executeWithTry("INSERT INTO if_procedureCompliance (procedureID,policyName) values (?,?)", [procedureid, policyname]);
    }

    IFDM.prototype.db_insert_systemPolicy = function(systemname, policyname) {
        this.executeWithTry("INSERT INTO if_systemPolicy (systemName,policyName) values (?,?)", [systemname, policyname]);
    }

    IFDM.prototype.db_insert_policyHierarchy = function(parentpolicyname, childpolicyname) {
        this.executeWithTry("INSERT INTO if_policyHierarchy (parentPolicyName,childPolicyName) values (?,?)", [parentpolicyname, childpolicyname]);
    }

    IFDM.prototype.db_insert_mimeType = function(mimetype, documenttype) {
        this.executeWithTry("INSERT INTO if_mimetype (mimeType,documentType) values (?,?)", [mimetype, documenttype]);
    }

    IFDM.prototype.db_insert_mimeExtensions = function(documentextension, mimetype) {
        this.executeWithTry("INSERT INTO if_mimeExtensions (documentExtension,mimeType) values (?,?)", [documentextension, mimetype]);
    }

    //Insert Document
    IFDM.prototype.db_insert_document = function(documentid, md5checksum, dateupdated, documentextension, url, documentname, documentdescription, imageurl, cachetext) {
        this.execute('INSERT INTO if_document (documentID, md5Checksum, dateUpdated, documentExtension) VALUES (?, ?, ?, ?)', [documentid, md5checksum, dateupdated, documentextension]);
        this.execute('INSERT INTO if_document_aux (rowid, URL, documentName, documentDescription, imageURL, cache) VALUES (last_insert_rowid(), ?, ?, ?, ?, ?)', [url, documentname, documentdescription, imageurl, cachetext]);
    }

    //Update Document
    IFDM.prototype.db_update_document_cache = function(documentid, documentversion, md5checksum, dateupdated, cachetext) {
        this.execute('UPDATE if_document SET DocumentVersion=?, MD5CheckSum=?, DateUpdated=? WHERE documentID=?', [documentversion, md5checksum, dateupdated, documentid]);
        this.execute('UPDATE if_document_aux SET Cache=? WHERE rowid=?', [cachetext, documentid]);
    }


    //Insert Procedure
    IFDM.prototype.db_insert_procedure = function(procedureID, procedureName, procedureDescription, purposeName) {
        this.execute('INSERT INTO if_procedure (procedureID, procedureName, procedureDescription, PurposeName) VALUES (?, ?, ?, ?)', [procedureID, procedureName, procedureDescription, purposeName]);
        this.execute('INSERT INTO if_procedure_aux_id (rowid, ProcedureID) ' +
               'VALUES (last_insert_rowid(), ?)', [procedureID]);
        this.execute('INSERT INTO if_procedure_aux_name (rowid, ProcedureName) ' +
               'VALUES (last_insert_rowid(), ?)', [procedureName]);
        this.execute('INSERT INTO if_procedure_aux_description (rowid, ProcedureDescription) ' +
               'VALUES (last_insert_rowid(), ?)', [procedureDescription]);
    }


    //Selects
    IFDM.prototype.db_selectDateTime_documentsIncremental = function() {
        var rs = this.execute('SELECT max(DateUpdated) as Latest from if_document');
        var result = 0;
        while (rs.isValidRow()) {
            result = rs.fieldByName('Latest');
            rs.next();
        }
        rs.close();
        return result;
    }

    IFDM.prototype.db_selectDateTime_documentsFull = function() {
        var rs = this.execute('SELECT TTValue as Latest from if_translatetable where TTKey=?', [ttLastFullDocs]);
        var result = 0;
        while (rs.isValidRow()) {
            result = rs.fieldByName('Latest');
            rs.next();
        }
        rs.close();
        return result;
    }

    IFDM.prototype.db_selectDateTime_diagrams = function() {
        var rs = this.execute('SELECT TTValue as Latest from if_translatetable where TTKey=?', [ttLatestDiagrams]);
        var result = 0;
        while (rs.isValidRow()) {
            result = rs.fieldByName('Latest');
            rs.next();
        }
        rs.close();
        return result;
    }

    IFDM.prototype.db_select_lastProcedureClicked = function() {
        var rs = this.execute('SELECT TTValue from if_translatetable where TTKey=?', [ttLastProcedureClicked]);
        var result = RootProcedureID;
        while (rs.isValidRow()) {
            result = rs.fieldByName('TTValue');
            rs.next();
        }
        rs.close();
        return result;
    }

    IFDM.prototype.db_selectAllTo_purposes = function() {
        var rs = this.execute('SELECT if_purpose.* from if_purpose');
        var purposes = new Array();
        while (rs.isValidRow()) {
            purposes[purposes.length] = new Purpose(rs.fieldByName('PurposeName'), rs.fieldByName('FlowchartElement'));
            rs.next();
        }
        rs.close();
        return purposes;
    }

    IFDM.prototype.db_selectAllTo_connectionTypes = function() {
        var rs = this.execute('SELECT if_connectionType.* from if_connectionType');
        var connectionTypes = new Array();
        while (rs.isValidRow()) {
            connectionTypes[connectionTypes.length] = new ConnectionType(rs.fieldByName('ConnectionType'), rs.fieldByName('ColorRGBHex'));
            rs.next();
        }
        rs.close();
        return connectionTypes;
    }

    IFDM.prototype.db_selectAllTo_groups = function() {
        var rs = this.execute('SELECT if_group.* from if_group');
        var groups = new Array();
        while (rs.isValidRow()) {
            groups[groups.length] = new Group(rs.fieldByName('GroupName'));
            rs.next();
        }
        rs.close();
        return groups;
    }


    IFDM.prototype.get_proceduresPopulated = function(procedureResultSet) {
        var rs = procedureResultSet;
        var rsObject = new Array();
        while (rs.isValidRow()) {
            //now replace the purpose string with the purpose object
            for (var i = 0; i < this.all_purposes.length; i++) {
                if (this.all_purposes[i].purposeName == rs.fieldByName('PurposeName')) {
                    //Create Procedure
                    rsObject[rsObject.length] = new Procedure(rs.fieldByName('ProcedureID'), rs.fieldByName('ProcedureName'), rs.fieldByName('ProcedureDescription'), this.all_purposes[i]);
                    //Add Groups (Add Everyone if none exist)
                    var rsGroups = this.execute('select * from if_procedureGroup where procedureID=?', [rs.fieldByName('ProcedureID')]);
                    while (rsGroups.isValidRow()) {
                        rsObject[rsObject.length - 1].AddGroup(new Group(rsGroups.fieldByName('GroupName')));
                        rsGroups.next();
                    }
                    rsGroups.close();
                    if (rsObject[rsObject.length - 1].groups.length == 0)
                        rsObject[rsObject.length - 1].AddGroup(new Group('Everyone'));
                    //Add Aliases
                    var rsAliases = this.execute('select * from if_procedureAliases where ProcedureID=?', [rs.fieldByName('ProcedureID')]);
                    while (rsAliases.isValidRow()) {
                        rsObject[rsObject.length - 1].AddAlias(rsAliases.fieldByName('Alias'));
                        rsAliases.next();
                    }
                    rsAliases.close();
                    //Add References
                    var rsReferencesParents = this.execute('select * from if_references where ProcedureDestination=?', [rs.fieldByName('ProcedureID')]);
                    while (rsReferencesParents.isValidRow()) {
                        rsObject[rsObject.length - 1].AddInboundReference(new Referenz(rsReferencesParents.fieldByName('ProcedureOrigin'), rsReferencesParents.fieldByName('ProcedureDestination')));
                        rsReferencesParents.next();
                    }
                    rsReferencesParents.close();
                    var rsReferencesChildren = this.execute('select * from if_references where ProcedureOrigin=?', [rs.fieldByName('ProcedureID')]);
                    while (rsReferencesChildren.isValidRow()) {
                        rsObject[rsObject.length - 1].AddOutboundReference(new Referenz(rsReferencesChildren.fieldByName('ProcedureOrigin'), rsReferencesChildren.fieldByName('ProcedureDestination')));
                        rsReferencesChildren.next();
                    }
                    rsReferencesChildren.close();
                    break;
                }
            }
            rs.next();
        }
        rs.close();
        return rsObject;
    }

    IFDM.prototype.db_select_procedures = function(procedureStringArray) {
        var aggregateProcedures = new Array();
        for (var i = 0; i < procedureStringArray.length; i++) {
            var rs = this.execute('select distinct if_procedure.* from if_procedure where procedureid=?', [procedureStringArray[i]]);
            var procs = this.get_proceduresPopulated(rs);
            for (var j = 0; j < procs.length; j++) {
                aggregateProcedures[aggregateProcedures.length] = procs[j];
            }
        }
        return aggregateProcedures;
    }


    IFDM.prototype.db_select_proceduresRoot = function() {
        var rs = this.execute('select distinct if_procedure.* from if_procedure left join if_connects on procedureid=if_connects.proceduredestination left join if_references on procedureid=if_references.proceduredestination where if_references.proceduredestination is null');
        //var rs = this.execute('select if_procedure.* from if_procedure left join if_connects on procedureid=proceduredestination where proceduredestination is null');
        return this.get_proceduresPopulated(rs);
    }

    IFDM.prototype.db_select_procedureConnectionsRoot = function() {
        var rs = this.execute('select cxn.* from if_connects cxn, if_procedure left join if_connects on procedureid=if_connects.proceduredestination where if_connects.proceduredestination is null and cxn.procedureorigin=procedureid');
        var rsObject = new Array();
        while (rs.isValidRow()) {
            for (var i = 0; i < this.all_connectionTypes.length; i++) {
                if (this.all_connectionTypes[i].connectionTypeName == rs.fieldByName('ConnectionType')) {
                    rsObject[rsObject.length] = new Connection(rs.fieldByName('ProcedureOrigin'), rs.fieldByName('ProcedureDestination'), this.all_connectionTypes[i]);
                    break;
                }
            }
            rs.next();
        }
        rs.close();
        return rsObject;
    }

    IFDM.prototype.db_select_procedureConnections = function(procedureID) {
        var history = new Array();
        var aggregateConnections = new Array();
        for (var i = -1; i < aggregateConnections.length; i++) {
            var connectionsUp = new Array();
            var connectionsDown = new Array();
            if (i == -1) {
                connectionsUp = this.db_select_procedureConnectionsUp(procedureID);
                connectionsDown = this.db_select_procedureConnectionsDown(procedureID);
                history[0] = procedureID;
                for (var k = 0; k < connectionsUp.length; k++) {
                    aggregateConnections[aggregateConnections.length] = connectionsUp[k];
                }
                for (var l = 0; l < connectionsDown.length; l++) {
                    aggregateConnections[aggregateConnections.length] = connectionsDown[l];
                }
            }
            if (i > -1) {
                var destinationChecked = false;
                for (var j = 0; j < history.length; j++) {
                    if (history[j] == aggregateConnections[i].destination) {
                        destinationChecked = true;
                        break;
                    }
                }
                var originChecked = false;
                for (var m = 0; m < history.length; m++) {
                    if (history[m] == aggregateConnections[i].origin) {
                        originChecked = true;
                        break;
                    }
                }
                if (!destinationChecked) {
                    connectionsUp = this.db_select_procedureConnectionsUp(aggregateConnections[i].destination);
                    connectionsDown = this.db_select_procedureConnectionsDown(aggregateConnections[i].destination);
                    history[history.length] = aggregateConnections[i].destination;
                    for (var k = 0; k < connectionsUp.length; k++) {
                        aggregateConnections[aggregateConnections.length] = connectionsUp[k];
                    }
                    for (var l = 0; l < connectionsDown.length; l++) {
                        aggregateConnections[aggregateConnections.length] = connectionsDown[l];
                    }
                }

                if (!originChecked) {
                    connectionsUp = this.db_select_procedureConnectionsUp(aggregateConnections[i].origin);
                    connectionsDown = this.db_select_procedureConnectionsDown(aggregateConnections[i].origin);
                    history[history.length] = aggregateConnections[i].origin;
                    for (var k = 0; k < connectionsUp.length; k++) {
                        aggregateConnections[aggregateConnections.length] = connectionsUp[k];
                    }
                    for (var l = 0; l < connectionsDown.length; l++) {
                        aggregateConnections[aggregateConnections.length] = connectionsDown[l];
                    }
                }
            }



        }
        //cleaning duplicates done elsewhere
        return aggregateConnections;
    }

    IFDM.prototype.db_select_procedureConnectionsDown = function(procedureID) {
        var rs = this.execute('select * from if_connects where ProcedureOrigin=?', [procedureID]);
        var rsObject = new Array();
        while (rs.isValidRow()) {
            var found = false;
            for (var i = 0; i < this.all_connectionTypes.length; i++) {
                if (this.all_connectionTypes[i].connectionTypeName == rs.fieldByName('ConnectionType')) {
                    rsObject[rsObject.length] = new Connection(rs.fieldByName('ProcedureOrigin'), rs.fieldByName('ProcedureDestination'), this.all_connectionTypes[i]);
                    found = true;
                    break;
                }
            }
            if (!found) {
                rsObject[rsObject.length] = new Connection(rs.fieldByName('ProcedureOrigin'), rs.fieldByName('ProcedureDestination'), new ConnectionType(null, null));
            }
            rs.next();
        }
        rs.close();
        return rsObject;
    }

    IFDM.prototype.db_select_procedureConnectionsUp = function(procedureID) {
        var rs = this.execute('select * from if_connects where ProcedureDestination=?', [procedureID]);
        var rsObject = new Array();
        while (rs.isValidRow()) {
            var found = false;
            for (var i = 0; i < this.all_connectionTypes.length; i++) {
                if (this.all_connectionTypes[i].connectionTypeName == rs.fieldByName('ConnectionType')) {
                    rsObject[rsObject.length] = new Connection(rs.fieldByName('ProcedureOrigin'), rs.fieldByName('ProcedureDestination'), this.all_connectionTypes[i]);
                    found = true;
                    break;
                }
            }
            if (!found) {
                rsObject[rsObject.length] = new Connection(rs.fieldByName('ProcedureOrigin'), rs.fieldByName('ProcedureDestination'), new ConnectionType(null, null));
            }
            rs.next();
        }
        rs.close();
        return rsObject;
    }

    IFDM.prototype.db_select_procedureConnectionsParents = function(procedureID) {
        //TODO: Go through mesh
        //get parent list from first one, if only one go up 1
        //recursively go through all peers and go up 1, if 1 AND ONLY 1 parent of ALL of peers in each diagram has 1 parent!

        var rs = this.execute('select * from if_connects where ProcedureDestination=?', [procedureID]);
        var rsObject = new Array();
        while (rs.isValidRow()) {
            for (var i = 0; i < this.all_connectionTypes.length; i++) {
                if (this.all_connectionTypes[i].connectionTypeName == rs.fieldByName('ConnectionType')) {
                    rsObject[rsObject.length] = new Connection(rs.fieldByName('ProcedureOrigin'), rs.fieldByName('ProcedureDestination'), this.all_connectionTypes[i]);
                    var connectionProcessed = false;
                    for (var j = 0; j < rsObject.length; j++) {
                        if (rsObject[j].destination == rs.fieldByName('ProcedureOrigin'))
                            connectionProcessed = true;
                    }
                    if (!connectionProcessed) {
                        //recursively add parents
                        var parentObject = this.db_select_procedureConnectionsParents(rs.fieldByName('ProcedureOrigin'));
                        //now add the parents
                        for (var k = 0; k < parentObject.length; k++) {
                            rsObject[rsObject.length] = parentObject[k];
                            //                            //go up as quick as possible (ignore other peers)
                            //                            if (parentObject[k].pointsToProcedure == true) {
                            //                                break;
                            //                            }
                            //                        }
                            //                        //go up as quick as possible (ignore other peers)
                            //                        if (rs.fieldByName('PointsToProcedure') != null) {
                            //                            break;
                            //                        }
                        }
                    }
                }
            }
            rs.next();
        }
        rs.close();
        return rsObject;
    }


    IFDM.prototype.db_select_procedure = function(procedureID) {
        var rs = this.execute('select if_procedure.* from if_procedure where ProcedureID=?', [procedureID]);
        return this.get_proceduresPopulated(rs);
    }

    IFDM.prototype.get_documentsPopulated = function(documentResultSet) {
        var rs = documentResultSet;
        var rsObject = new Array();
        while (rs.isValidRow()) {
            rsObject[rsObject.length] = new Dokument(rs.fieldByName('DocumentID'), rs.fieldByName('DocumentName'), rs.fieldByName('DocumentDescription'), rs.fieldByName('URL'), rs.fieldByName('ImageURL'), rs.fieldByName('MD5Checksum'), rs.fieldByName('DateUpdated'), rs.fieldByName('DocumentExtension'));
            rs.next();
        }
        rs.close();
        return rsObject;
    }

    IFDM.prototype.db_select_documents = function(procedureID) {
        var rs = this.execute('SELECT if_document.*, URL, documentName,documentDescription, imageURL from if_document inner join if_proceduredocuments on if_document.documentID=if_procedureDocuments.documentID inner join if_document_aux on if_document.documentID=if_document_aux.rowid where if_proceduredocuments.procedureID=?', [procedureID]);
        var docs = this.get_documentsPopulated(rs);
        if (inheritsOrphanReference) {
            var orphanReferences = this.execute('select if_references.proceduredestination from if_references left outer join if_connects as ifc on ifc.proceduredestination=if_references.proceduredestination left outer join if_connects as ifx on ifx.procedureorigin=if_references.proceduredestination where if_references.procedureorigin=? and ifx.procedureorigin is null and ifx.proceduredestination is null and ifc.procedureorigin is null and ifc.proceduredestination is null', [procedureID]);
            while (orphanReferences.isValidRow()) {
                var referenceDocuments = this.execute('SELECT if_document.*, URL, documentName,documentDescription, imageURL from if_document inner join if_proceduredocuments on if_document.documentID=if_procedureDocuments.documentID inner join if_document_aux on if_document.documentID=if_document_aux.rowid where if_proceduredocuments.procedureID=?', [orphanReferences.fieldByName('ProcedureDestination')]);
                var refDocs = this.get_documentsPopulated(referenceDocuments);
                for (var i = 0; i < refDocs.length; i++) {
                    docs[docs.length] = refDocs[i];
                }
                orphanReferences.next();
            }
            orphanReferences.close();
        }
        return docs;
    }


    //Parse
    IFDM.prototype.parse_diagram = function(procedureID) {
        if (procedureID == null) {
            var diagramRoot = new Diagram(null, null, this.db_select_proceduresRoot(), this.db_select_procedureConnectionsRoot());
            return diagramRoot;
        }

        //We want to get all the related connections
        //THEN populate the diagram with constituent procedures
        //method
        //follow each procedure up and down recursively (connection)
        //just add references (pointers to other procedures in other diagrams) where possible
        //then append to connectionList
        //after, then generate procedure list (do not need PointsToProcedure!=null)
        var connectionTree = this.db_select_procedureConnections(procedureID);
        var procedures = new Array();
        var procedureParent = null;
        var procedureNames = new Array();
        if (connectionTree.length > 0) {
            for (var i = 0; i < connectionTree.length; i++) {
                //first check that procedure has not been processed
                var originFound = false;
                var destinationFound = false;
                for (var j = 0; j < procedures.length; j++) {
                    //ignore existing procedures or detail destinationProcedures
                    if (procedures[j].procedureID == connectionTree[i].destination) {
                        destinationFound = true;
                    }
                    if (procedures[j].procedureID == connectionTree[i].origin) {
                        originFound = true;
                    }
                }
                if (!originFound) {
                    procedureNames[procedureNames.length] = PREFIX + connectionTree[i].origin + SUFFIX;
                    var procedureArray = this.db_select_procedure(connectionTree[i].origin);
                    if (procedureArray.length == 1) {
                        procedures[procedures.length] = procedureArray[0];
                        if (procedures[procedures.length - 1].procedureID == procedureID) {
                            procedureParent = procedures[procedures.length - 1];
                        }
                    }
                }
                if (!destinationFound) {
                    procedureNames[procedureNames.length] = PREFIX + connectionTree[i].destination + SUFFIX;
                    var procedureArray = this.db_select_procedure(connectionTree[i].destination);
                    if (procedureArray.length == 1) {
                        procedures[procedures.length] = procedureArray[0];
                        //                    if (procedures[procedures.length - 1].procedureID == procedureID) {
                        //                        procedureParent = procedures[procedures.length - 1];
                        //                    }
                    }
                }
            }
            //alert(diagramID);
            var diagram = new Diagram(procedureNames.sort().join(), procedureParent, procedures, connectionTree);
            return diagram;
        }
        else {
            var procedureArray = this.db_select_procedure(procedureID);
            if (procedureArray.length > 0) {
                procedures[0] = procedureArray[0];
                var diagram = new Diagram(PREFIX + procedureArray[0].procedureID + SUFFIX, procedureArray[0], procedures, connectionTree);
                return diagram;
            }
            else {
                alert('No Diagrams');
                return null;
            }
        }
    }

    //Search Procedure
    IFDM.prototype.db_search_procedure_id = function(searchTerm) {
        var rs = this.execute('select if_procedure.* FROM if_procedure INNER JOIN if_procedure_aux_id ' +
                  ' ON if_procedure.rowid = if_procedure_aux_id.rowid WHERE if_procedure_aux_id MATCH ?',
                    [searchTerm]);
        return this.get_proceduresPopulated(rs);
    }
    IFDM.prototype.db_search_procedure_name = function(searchTerm) {
        var rs = this.execute('SELECT if_procedure.* FROM if_procedure INNER JOIN if_procedure_aux_name ' +
                  ' ON if_procedure.rowid = if_procedure_aux_name.rowid WHERE if_procedure_aux_name MATCH ?',
                    [searchTerm]);
        return this.get_proceduresPopulated(rs);
    }
    IFDM.prototype.db_search_procedure_description = function(searchTerm) {
        var rs = this.execute('SELECT if_procedure.* FROM if_procedure INNER JOIN if_procedure_aux_description ' +
                  ' ON if_procedure.rowid = if_procedure_aux_description.rowid WHERE if_procedure_aux_description MATCH ?',
                    [searchTerm]);
        return this.get_proceduresPopulated(rs);
    }
    IFDM.prototype.db_search_procedure_aliases = function(searchTerm) {
        var rs = this.execute('SELECT if_procedure.* FROM if_procedure INNER JOIN if_procedureAliases_aux ON if_procedure.procedureid=if_procedureAliases_aux.procedureid WHERE if_procedureAliases_aux MATCH ?',
                    [searchTerm]);
        return this.get_proceduresPopulated(rs);
    }

    //Search Document
    IFDM.prototype.db_search_document = function(searchTerm) {
        var rs = this.execute('SELECT if_document.*, URL, documentName,documentDescription, imageURL from if_document inner join if_document_aux on if_document.documentID=if_document_aux.rowid'
            + ' where if_document_aux MATCH ?', [searchTerm]);
        return this.get_documentsPopulated(rs);
    }


    //Procedures
    IFDM.prototype.cache_data = function() {
        //Copy all DB tables from server
        //not implemented here yet as require code service access
    }

    //Internal docs required to go offline (minimum)
    IFDM.prototype.cache_internal_documents = function() {
        var onlineStatus = this.localStore.enabled;
        try {
            this.localStore.enabled = false;
            this.localStore.capture(FilesToCapture, this.cache_updated);
        }
        catch (e) { }
        this.localStore.enabled = onlineStatus;
        //store.manifestUrl = 'site-manifest.txt'; //not a managed store
        //store.checkForUpdate();	
    }

    //Anything Else
    IFDM.prototype.cache_documents = function(urlOrURLArray) {
        //        var onlineStatus = this.localStore.enabled;
        //        try {
        //            this.localStore.enabled = false;
        //            this.localStore.capture(urlOrURLArray, this.cache_updated);
        //        }
        //        catch (e) { }
        //        this.localStore.enabled = onlineStatus;
        this.localStore.capture(urlOrURLArray, this.cache_updated);

    }


    IFDM.prototype.cache_updated = function(url, success, captureId) {
        trace('*CACHE* docURL: %s, IsSuccess: %s, CaptureID: %s', [url, success, captureId]);
    }

    IFDM.prototype.db_data_test = function() {
        this.execute('begin');

        for (var i = 0; i < 40; i++) {
            this.execute('insert into Demo values (?, ?)', ['Monkey!', new Date().getTime()]);
        }
        this.execute('commit')
        var rs = this.execute('select count(*) from Demo order by Timestamp desc');
        while (rs.isValidRow()) {
            document.write(rs.field(0));
            rs.next();
        }
        rs.close();
        return true;
    }

    //FINALLY
    if (!window.InnovationFoundryJS) {
        InnovationFoundryJS = {};
    }
    if (!InnovationFoundryJS.DataManager) {
        InnovationFoundryJS.DataManager = new IFDM(false);
    }

})();
